package com.example.skycodes.dao;

import com.example.skycodes.bean.NewsBean;
import com.example.skycodes.util.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;


    public class NewsDao<newsBean> {
        public static NewsDao newsDao;
        DBConnection DBConn = new DBConnection();

        // todo 后期添加分页功能
        public List<NewsBean> getList(int page) {
            Connection conn = DBConn.getConn();
            String sql = "select * from news limit " + (page - 1) * 10 + ",10";
            List<NewsBean> list = new ArrayList<>();
            list = null;
            try {
                Statement statement = conn.createStatement();
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    NewsBean newsBean = new NewsBean();
                    newsBean.setNewsID(rs.getInt("NewsID"));
                    newsBean.setNewsTitle(rs.getString("NewsTitle"));
                    newsBean.setNewsContent(rs.getString(3));
                    newsBean.setNewsTime(rs.getString("NewsTime"));
                    newsBean.setAdminName(rs.getString("AdminName"));
                    list.add(newsBean);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return list;
        }

        /**
         * 添加新闻
         *
         * @param news
         * @return
         */

        public int add(NewsBean news) {
            Connection conn = DBConn.getConn();
            String sql = "insert into news values(null,?,?,?)";
            try {
                PreparedStatement pmst = conn.prepareStatement(sql);
                pmst.setString(1, news.getNewsTitle());
                pmst.setString(2, news.getNewsContent());
                pmst.setString(3, (new Date()).toString());
                return pmst.executeUpdate(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }

        /**
         * 更新功能
         *
         * @param news
         * @return
         */
        public int update(NewsBean news) {
            Connection conn = DBConn.getConn();
            String sql = "update news set NewsTitle=?,NewsConten=?,NewsTime=?,NewsName=? where NewsID=?";

            try {
                PreparedStatement pmst = conn.prepareStatement(sql);
                pmst.setString(1, news.getNewsTitle());
                pmst.setString(2, news.getNewsContent());
                pmst.setString(3, (new Date()).toString());
                return pmst.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }

        public int delete(int newsID) {
            Connection conn = DBConn.getConn();
            try {
                String sql = "delete from news where NewID=?";
                PreparedStatement pmst = conn.prepareStatement(sql);
                pmst.setInt(1, newsID);
                return pmst.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }

        /**
         * 统计新闻总条数
         *
         * @return
         */
        public int count() {
            Connection conn = DBConn.getConn();
            try {
                String sql = "select count(*) from news";
                Statement statement = conn.createStatement();
                ResultSet rs = statement.executeQuery(sql);
                if (rs.next()) {
                    return rs.getInt(1);//cout查询返回一行数据 第一行 第一列就是总条数
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }

        public NewsBean getNewsByID(int id) throws SQLException {
            try {
                String sql = "select * from news where NewsID=?";
                NewsBean newsBean = new NewsBean();
                Connection conn = DBConn.getConn();
                PreparedStatement pmst = conn.prepareStatement(sql);
                pmst.setInt(1, id);
                ResultSet rs = pmst.executeQuery();
                if (rs.next()) {
                    newsBean.setAdminID(rs.getInt(1));
                    newsBean.setNewsTitle(rs.getString(2));
                    newsBean.setNewsContent(rs.getString(3));
                    newsBean.setNewsTime(rs.getString(3));
                    newsBean.setAdminName(rs.getString(3));
                    return newsBean;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    }




